﻿using System;
using System.Collections.Generic;
//using Infragistics.Excel;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Threading;
using System.Windows.Shapes;
using System.Windows.Forms;
using System.Data;
using CMISLibrary.Libraries;
using CMISLibrary;
using System.Media;
using System.Threading;
//using busQuanLyMatDien;
using BCNPCEntities;
using BaocaoNPC.common;
using System.Windows.Controls.Primitives;
//using Infragistics.Windows.DataPresenter.ExcelExporter;

namespace BaocaoNPC.Usercontrols
{
    /// <summary>
    /// Interaction logic for Import_Excel.xaml
    /// </summary>
    public partial class Import_Excel : System.Windows.Controls.UserControl
    {
        #region Khai báo biến
        public String strMa_dviqly = NPCUS_DanhMuc.Pglobal.strMa_dviqly != "" && NPCUS_DanhMuc.Pglobal.strMa_dviqly != null ? NPCUS_DanhMuc.Pglobal.strMa_dviqly : "PA";
        public String strTenDNHap = NPCUS_DanhMuc.Pglobal.strTen_dangnhap != "" && NPCUS_DanhMuc.Pglobal.strTen_dangnhap != null ? NPCUS_DanhMuc.Pglobal.strTen_dangnhap : "PA";
        public String strTen_dviqly = NPCUS_DanhMuc.Pglobal.strTen_dviqly != "" && NPCUS_DanhMuc.Pglobal.strTen_dviqly != null ? NPCUS_DanhMuc.Pglobal.strTen_dviqly : "cty..";
        public String strTen_dviqly_CT = NPCUS_DanhMuc.Pglobal.strTen_dviqly_CT != "" && NPCUS_DanhMuc.Pglobal.strTen_dviqly_CT != null ? NPCUS_DanhMuc.Pglobal.strTen_dviqly_CT : "cap tren";
        //bus_TienichNPC.cls_ImportExcel_bus cls = new bus_TienichNPC.cls_ImportExcel_bus();
        DataSet DsMATUSINHKH = new DataSet();
        DataSet DsMATUSINHHD = new DataSet();
        DataSet DsTableInsert = new BCNPCEntities.EXCEL.DstableExcel();
        DataSet DsTableDelete = new BCNPCEntities.EXCEL.dstableDelete();
        DataSet dsFillSearch;
        DataTable dtExecl;
        string ma_khang;
        string ma_hdong;
        string filename;
        string maloi;
        string chuoi="";
        string nguoinhap = "CMISEXCEL";
        Int64 id_MaYeuCau;
        Int64 id_hopdong;
        #endregion
        public Import_Excel()
        {
            InitializeComponent();
            dtpThangNam.SelectedDate = System.DateTime.Now;
            proBExcel.Visibility = Visibility.Hidden;
            cmdImportExcel.IsEnabled = false;
        }
        public DataTable GetDataTableExcel(string datasource)
        {
            try
            {
                System.Data.OleDb.OleDbConnection theConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" + datasource + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"");
                theConnection.Open();

                System.Data.OleDb.OleDbDataAdapter theDataAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", theConnection);

                DataTable dt = new DataTable();

                theDataAdapter.Fill(dt);

                return dt;
            }
            catch
            { return null; }
        }
        private void cmdChonDuongDan_Click(object sender, RoutedEventArgs e)
        {
            Microsoft.Win32.OpenFileDialog OpenFileDialog = new Microsoft.Win32.OpenFileDialog();
            OpenFileDialog.Filter = "Execl files (*.xls)|*.xls";
            OpenFileDialog.FilterIndex = 0;
            OpenFileDialog.RestoreDirectory = true;
            OpenFileDialog.CheckFileExists = true;
            OpenFileDialog.Title = "Select file Excel!";
            OpenFileDialog.ShowDialog();
            filename = OpenFileDialog.FileName;
            txtDuongDan.Text = filename;
        }
        private void cmdView_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                proBExcel.Visibility = Visibility.Hidden;
                txtphantram.Content = "";
                txtts.Content = "";                
                dtExecl = new DataTable();
                dtExecl = GetDataTableExcel(filename);
                if (dtExecl == null || dtExecl.Rows.Count == 0)
                {
                    System.Windows.MessageBox.Show("File excel không có dữ liệu hoặc tên Sheet ko đúng! Phải đặt tên Sheet là: Sheet1", "Import Excel");
                    return;
                }
                for (int i = 0; i < dtExecl.Columns.Count ; i++)
                {
                    dgrShow.Columns[i].Header = dtExecl.Rows[0][i].ToString();
                }
                dtExecl.Rows.RemoveAt(0);
                dgrShow.ItemsSource = dtExecl.DefaultView;
                if (dtExecl.Rows.Count > 0)
                {
                    cmdImportExcel.IsEnabled = true;
                }
                else
                { cmdImportExcel.IsEnabled = false; }
                txtrowinsert.Content = "Tổng số: " + dtExecl.Rows.Count;
                txtphantram.Content = "";
                txtts.Content = "";
            }
            catch(Exception ex)
            { SetStatus("ERROR", cmdView, ex.ToString()); }

            //dgvreceipient.ItemsSource = dt.DefaultView;=========>the problem that this ligne will show another column 
        }
        private void Check_ngaythang(DataTable dt)
        {
            try
            {
                chuoi = "";
                for (int i = 0; i < dt.Columns.Count - 1; i++)
                {

                }
            }
            catch
            { }
        }
        private string khoitaomakhang(string txt)
        {
            string s="";
            if(txt.Length ==1)
            {
                 s = strMa_dviqly + "000000" + txt.ToString();
                 //return s;
            }
            if (txt.Length == 2)
            {
                 s = strMa_dviqly + "00000" + txt.ToString();
                 //return s;
            }
            if (txt.Length == 3)
            {
                 s = strMa_dviqly + "0000" + txt.ToString();
                 //return s;
            }
            if (txt.Length == 4)
            {
                 s = strMa_dviqly + "000" + txt.ToString();
                 //return s;
            }
            if (txt.Length == 5)
            {
                 s = strMa_dviqly + "00" + txt.ToString();
                 //return s;
            }
            if (txt.Length == 6)
            {
                 s = strMa_dviqly + "0" + txt.ToString();
                 //return s;
            }
            if (txt.Length == 7)
            {
                 s = strMa_dviqly + txt.ToString();
                 //return s;
            }
            return s;
        }
        private string khoitaomahopdong(string matiento,string txt)
        {
            string s="";
            if (txt.Length == 1)
            {
                s = matiento + "00000" + txt.ToString();
                //return s;
            }
            if (txt.Length == 2)
            {
                s = matiento + "0000" + txt.ToString();
                //return s;
            }
            if (txt.Length == 3)
            {
                s = matiento + "000" + txt.ToString();
                //return s;
            }
            if (txt.Length == 4)
            {
                s = matiento + "00" + txt.ToString();
               // return s;
            }
            if (txt.Length == 5)
            {
                s = matiento + "0" + txt.ToString();
               // return s;
            }
            if (txt.Length == 6)
            {
                s = matiento + txt.ToString();
                //return s;
            }
            return s;
        }
        private void khoitaotable()
        {
            DsTableDelete.Tables["DV_YCAU_KNAI"].Rows.Clear();
            DsTableDelete.Tables["DD_BBAN_BDONG"].Rows.Clear();
            DsTableDelete.Tables["DD_BCS_CTO"].Rows.Clear();
            DsTableDelete.Tables["DD_BDONG_CTO"].Rows.Clear();
            DsTableDelete.Tables["DD_BDONG_HTAI"].Rows.Clear();
            DsTableDelete.Tables["HDG_PTHUC_TTOAN"].Rows.Clear();
            DsTableDelete.Tables["GCS_CHISO"].Rows.Clear();
            DsTableDelete.Tables["HDG_BBAN_APGIA"].Rows.Clear();
            DsTableDelete.Tables["HDG_DDO_SOGCS"].Rows.Clear();
            DsTableDelete.Tables["HDG_DIEM_DO"].Rows.Clear();
            DsTableDelete.Tables["HDG_HO_NGHEO"].Rows.Clear();
            DsTableDelete.Tables["HDG_HOP_DONG"].Rows.Clear();
            DsTableDelete.Tables["HDG_KHACH_HANG"].Rows.Clear();
            DsTableDelete.Tables["HDG_KHANG_LIENHE"].Rows.Clear();
            DsTableDelete.Tables["HDG_VITRI_DDO"].Rows.Clear();
            DsTableDelete.Tables["HDG_PHU_LUC_HD"].Rows.Clear();
            DsTableDelete.Tables["DD_BDONG_TTHAO"].Rows.Clear();
        }
        private void cmdImportExcel_Click(object sender, RoutedEventArgs e)
        {
            Int64 id_KH_tusinh;
            Int64 id_HD_tusinh;
            Int64 id_matusinhHD,id_matusinhKH;
            Int64 id_DVDIACHINH;
            Int64 id_bb_bdong=0;
            Int64 id_bcs = 0, id_qhe = 0, id_vitri_ddo = 0,id_pthuc_ttoan =0,id_lienhe =0,id_chiso=0,id_bbanagia =0,id_hongheo=0;
            Int64 ID_LSU_TTHAO = 0, ID_PHU_LUC_HD = 0;
            string ma_tiento;
            int dem = 0, demct = 0, demso = 0, demdc = 0, demtram = 0;
            DataSet dsc= new DataSet();

            string thongbao = "", thongbaoct = "";
            try
            {
                if (dtExecl == null && dtExecl.Rows.Count < 0)
                {
                    SetStatus("ERROR", cmdImportExcel, "Không có dữ liệu cập nhật!");
                    return;
                }
                khoitaotable();
                // kiểm tra ngày nhập
                if (dtpThangNam.Text == string.Empty)
                {
                    System.Windows.MessageBox.Show("Chưa chọn ngày nhập khách hàng!");
                    dtpThangNam.Focus();
                    return;
                }
                //
                SetStatus("OK", cmdImportExcel, "Đang kiểm tra dữ liệu đầu vào!");
                
                #region kiểm tra công tơ có tồn tại và sổ có được lập lịch hay chưa
                if (dtExecl.Rows.Count > 0)
                {
                    DataRow[] rgchechct = dtExecl.Select();
                    for (int i = 0; i < dtExecl.Rows.Count; i++)
                    {
                        #region kiem tra dữ liệu null
                        if(dtExecl.Rows[i]["F2"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 2" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F14"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 14" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F16"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 16" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F17"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 17" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F18"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 18" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F19"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 19" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F20"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 20" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F21"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 21" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F22"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 22" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F23"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 23" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F24"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i + 1).ToString() + ", Cột 24" + "\r\n";
                            dem = 1;
                            break;
                        }
                        if(dtExecl.Rows[i]["F25"].ToString().Trim() == "")
                        {
                            thongbao = "Dòng " + (i+1).ToString() + ", Cột 25"  + "\r\n";
                            dem = 1;
                            break;
                        }
                        #endregion

                        // kiem tra dia chinh
                        bool d = GlobalServices.Service_NPCUS.checkdonvidiachinh(strMa_dviqly, dtExecl.Rows[i]["F9"].ToString());
                        //bool d = cls.checkdonvidiachinh(strMa_dviqly, dtExecl.Rows[i]["F9"].ToString());
                        if (d == false)
                        {
                            thongbao = thongbao + "Dòng " + (i + 1).ToString() + " : Mã địa chính " + rgchechct[i]["F9"].ToString() + "\r\n";
                            demdc = 1;
                            break;
                        }
                        //kiem tra sogcs
                        bool t = GlobalServices.Service_NPCUS.checktram(strMa_dviqly, dtExecl.Rows[i]["F13"].ToString());
                        //bool t = cls.checktram(strMa_dviqly, dtExecl.Rows[i]["F13"].ToString());
                       if (t == false)
                       {
                           thongbao = thongbao + "Dòng " + (i + 1).ToString() + " : Mã trạm " + rgchechct[i]["F13"].ToString() + "\r\n";
                           demtram = 1;
                           break;
                       }
                        // kiem tra ma tram
                       bool s = GlobalServices.Service_NPCUS.check_sogcs(strMa_dviqly, dtExecl.Rows[i]["F15"].ToString(), dtpThangNam.SelectedDate.Value.Month, dtpThangNam.SelectedDate.Value.Year);
                       //bool s = cls.check_sogcs(strMa_dviqly, dtExecl.Rows[i]["F15"].ToString(), dtpThangNam.SelectedDate.Value.Month, dtpThangNam.SelectedDate.Value.Year);
                       if (s == false)
                       {
                           thongbao = thongbao + "Dòng " + (i + 1).ToString() + " : Mã sổ GCS " + rgchechct[i]["F15"].ToString() + "\r\n";
                           demso = 1;
                           break;
                       }
                       // kiem tra cong to
                       dsc = GlobalServices.Service_NPCUS.check_cto(strMa_dviqly, rgchechct[i]["F22"].ToString(), rgchechct[i]["F21"].ToString(), rgchechct[i]["F23"].ToString());
                       //dsc = cls.check_cto(strMa_dviqly, rgchechct[i]["F22"].ToString(), rgchechct[i]["F21"].ToString(), rgchechct[i]["F23"].ToString());
                       if (dsc == null || dsc.Tables[0].Rows.Count == 0)
                       {
                           thongbaoct = thongbaoct + "Dòng " + (i + 1).ToString() + " : công tơ " + rgchechct[i]["F22"].ToString() + "\r\n";
                           demct = 1;
                           //break;
                       }
                    }
                    
                }
                if( dem >0)
                {
                    System.Windows.MessageBox.Show(thongbao + "Không được để trống", "Import Excel!");
                    SetStatus("ERROR", cmdImportExcel, "");
                    dem = 0;
                    return;
                }
                if (demdc > 0)
                {
                    System.Windows.MessageBox.Show(thongbao + "Không tồn tại!", "Import Excel!");
                    SetStatus("ERROR", cmdImportExcel, "");
                    demdc = 0;
                    return;
                }
                if (demtram > 0)
                {
                    System.Windows.MessageBox.Show(thongbao + "Không tồn tại hoặc đã hết hiệu lực!", "Import Excel!");
                    SetStatus("ERROR", cmdImportExcel, "");
                    demtram = 0;
                    return;
                }
                if (demso > 0)
                {
                    System.Windows.MessageBox.Show(thongbao + "Không tồn tại sổ ghi chỉ số hoặc hết hiệu lực hoặc sổ chưa được lập lịch", "Import Excel!");
                    SetStatus("ERROR", cmdImportExcel, "");
                    demso = 0;
                    return;
                }
                if (demct > 0)
                {
                    System.Windows.MessageBox.Show(thongbaoct+ "Không tồn tại trong hồ sơ công tơ hoặc công tơ đã được treo, đề nghị kiểm tra lại!", "Import Excel!");
                    SetStatus("ERROR", cmdImportExcel, "");
                    demct = 0;
                    return;
                }
                    
                #endregion

                #region lấy id đơn vị địa chính

                id_DVDIACHINH = GlobalServices.Service_NPCUS.ID_DVI_DCHINH(strMa_dviqly);
                //id_DVDIACHINH = cls.ID_DVI_DCHINH(strMa_dviqly);
                if (id_DVDIACHINH == -1)
                {
                    SetStatus("ERROR", cmdImportExcel, "Không tồn tại ID đơn vị địa chính! đề nghị kiểm tra lại"); return; 
                }
                #endregion

                #region lấy thông tin mã tự sinh của khách hàng và hợp đồng
                DsMATUSINHKH = new DataSet();
                DsMATUSINHHD = new DataSet();
                DsMATUSINHKH = GlobalServices.Service_NPCUS.select_MATUSINH(strMa_dviqly, "KH");
                //DsMATUSINHKH = cls.select_MATUSINH(strMa_dviqly, "KH");
                id_KH_tusinh = Convert.ToInt64(DsMATUSINHKH.Tables[0].Rows[0]["MA_TU_SINH"].ToString());
                id_KH_tusinh++;
                id_matusinhKH = Convert.ToInt64(DsMATUSINHKH.Tables[0].Rows[0]["ID_HDG_MATUSINH"].ToString());

                DsMATUSINHHD = GlobalServices.Service_NPCUS.select_MATUSINH(strMa_dviqly, "HD");
                //DsMATUSINHHD = cls.select_MATUSINH(strMa_dviqly, "HD");
                id_HD_tusinh = Convert.ToInt64(DsMATUSINHHD.Tables[0].Rows[0]["MA_TU_SINH"].ToString());
                id_HD_tusinh++;
                id_matusinhHD = Convert.ToInt64(DsMATUSINHHD.Tables[0].Rows[0]["ID_HDG_MATUSINH"].ToString());
                ma_tiento = DsMATUSINHHD.Tables[0].Rows[0]["MA_TIEN_TO"].ToString();
                
                #endregion

                //chay ProgressBar
                //System.Windows.Controls.ProgressBar proBExcel = new System.Windows.Controls.ProgressBar();
                proBExcel.Visibility = Visibility.Visible;
                proBExcel.Minimum = 0;
                proBExcel.Maximum = 100;
                proBExcel.Value = 0;
                txtphantram.Content = "0%";
                txtts.Content = "0/" + dtExecl.Rows.Count;
                //
                SetStatus("OK", cmdImportExcel, "Bắt đầu cập nhật số liệu!");
                if (dtExecl.Rows.Count > 0)
                {
                    DataRow[] rgINS = dtExecl.Select();
                    for (int i = 0; i < dtExecl.Rows.Count; i++)
                    {
                        // khởi tạo mã khách hàng
                        ma_khang = khoitaomakhang(id_KH_tusinh.ToString());
                        id_KH_tusinh++;
                        //
                        //khởi tạo mã hợp đồng
                        ma_hdong = khoitaomahopdong(ma_tiento, id_HD_tusinh.ToString());
                        id_HD_tusinh++;
                        DateTime dennam = dtpThangNam.SelectedDate.Value.AddYears(5);
                        //
                        #region insert bảng DV_YCAU_KNAI
                        DsTableInsert.Tables["DV_YCAU_KNAI"].Rows.Clear();
                        DataRow drDV_YCAU_KNAI = DsTableInsert.Tables["DV_YCAU_KNAI"].NewRow();
                        drDV_YCAU_KNAI["MA_DVIQLY"] = strMa_dviqly;
                        //drDV_YCAU_KNAI["MA_YCAU_KNAI"] = rgINS[i]["MA_PTDIEN"].ToString();
                        //drDV_YCAU_KNAI["SO_PHIEU_YCAU"] = RESULT_PTU_MC(dsResultMCAT2, txtTbiMC2.Text);
                        drDV_YCAU_KNAI["ID_VITRI_HSO"] = "";
                        drDV_YCAU_KNAI["MA_KHANG_YCAU"] = ma_khang;
                        drDV_YCAU_KNAI["MA_KHANG"] = ma_khang;
                        drDV_YCAU_KNAI["TEN_NGUOIYCAU"] = rgINS[i]["F2"].ToString();
                        drDV_YCAU_KNAI["DCHI_NGUOIYCAU"] = rgINS[i]["F3"].ToString(); ;
                        drDV_YCAU_KNAI["NGAY_TNHAN"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drDV_YCAU_KNAI["NOI_DUNG_YCAU"] = "YEU CAU MUA DIEN";
                        drDV_YCAU_KNAI["NGAY_HTHANH"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drDV_YCAU_KNAI["MA_TCHAT"] = "KN";
                        drDV_YCAU_KNAI["MA_HTHUC"] = "TT";
                        drDV_YCAU_KNAI["MA_LOAI_YCAU"] = "CDIEN";
                        drDV_YCAU_KNAI["TINH_TRANG"] = 1;
                        drDV_YCAU_KNAI["DTHOAI"] = "";
                        drDV_YCAU_KNAI["MA_CNANG"] = "-1";
                        drDV_YCAU_KNAI["NGAY_TAO"] = DateTime.Now;
                        drDV_YCAU_KNAI["NGUOI_TAO"] = nguoinhap;
                        drDV_YCAU_KNAI["NGAY_SUA"] = DateTime.Now;
                        drDV_YCAU_KNAI["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["DV_YCAU_KNAI"].Rows.Add(drDV_YCAU_KNAI);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_DV_YCAU_KNAI(DsTableInsert, ref id_MaYeuCau, ref maloi))
                        //if (!cls.insert_DV_YCAU_KNAI(DsTableInsert,ref id_MaYeuCau, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóa
                        DataRow drDV_YCAU_KNAI_del = DsTableDelete.Tables["DV_YCAU_KNAI"].NewRow();
                        drDV_YCAU_KNAI_del["MA_DVIQLY"] = strMa_dviqly;
                        drDV_YCAU_KNAI_del["MA_YCAU_KNAI"] = id_MaYeuCau;
                        DsTableDelete.Tables["DV_YCAU_KNAI"].Rows.Add(drDV_YCAU_KNAI_del);
                        DsTableDelete.AcceptChanges();
                        #endregion

                        #region insert bảng HDG_HOP_DONG

                        DsTableInsert.Tables["HDG_HOP_DONG"].Rows.Clear();
                        DataRow drHDG_HOP_DONG = DsTableInsert.Tables["HDG_HOP_DONG"].NewRow();
                        drHDG_HOP_DONG["MA_DVIQLY"] = strMa_dviqly;
                        //drHDG_HOP_DONG["ID_HDONG"] = rgINS[i]["MA_PTDIEN"].ToString();
                        drHDG_HOP_DONG["MA_HDONG"] = ma_hdong;
                        drHDG_HOP_DONG["MA_YCAU_KNAI"] = id_MaYeuCau;
                        drHDG_HOP_DONG["MA_LOAIHD"] = 1;
                        drHDG_HOP_DONG["NGAYKY_HDONG"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_HOP_DONG["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_HOP_DONG["TRANG_THAI"] = 1;
                        drHDG_HOP_DONG["HLUC_TUNGAY"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_HOP_DONG["HLUC_DENNGAY"] = dennam;
                        drHDG_HOP_DONG["MA_LDO_TLY"] = "";
                        drHDG_HOP_DONG["THANG"] = dtpThangNam.SelectedDate.Value.Month;
                        drHDG_HOP_DONG["NAM"] = dtpThangNam.SelectedDate.Value.Year;
                        drHDG_HOP_DONG["SONAM_HLUC"] = 5;
                        drHDG_HOP_DONG["STIEN_DCOC"] = "";
                        drHDG_HOP_DONG["KWH_TDUONG"] = "";
                        drHDG_HOP_DONG["TGIAN_TDUONG"] = "";
                        drHDG_HOP_DONG["LOAI_TIEN"] = "VND";
                        drHDG_HOP_DONG["DIEN_AP"] = "220";
                        drHDG_HOP_DONG["TBAO_SUCO"] = 0;
                        drHDG_HOP_DONG["DAP_DDONG"] = 5;
                        drHDG_HOP_DONG["TAN_SO"] = "50";
                        drHDG_HOP_DONG["TANSO_SAISO"] = "0.2";
                        drHDG_HOP_DONG["NGAY_GCS"] = rgINS[i]["F19"].ToString();
                        drHDG_HOP_DONG["SOLAN_TTOAN"] = 1;
                        drHDG_HOP_DONG["THOIHAN_TTOAN"] = "15";
                        drHDG_HOP_DONG["SNAM_GHAN"] = 5;
                        drHDG_HOP_DONG["DCHI_TTOAN"] = rgINS[i]["F3"].ToString();
                        drHDG_HOP_DONG["FILE_DKEM"] = "";
                        drHDG_HOP_DONG["TTHUAN_KHAC"] = "";
                        drHDG_HOP_DONG["NHAN_HDON"] = 1;
                        drHDG_HOP_DONG["MA_HTHUC_GHDON"] = 1;
                        drHDG_HOP_DONG["HTHUC_TBAO"] = "";
                        drHDG_HOP_DONG["CQUAN_TRTAI"] = "";
                        drHDG_HOP_DONG["TLEPHAT_BMUA"] = "";
                        drHDG_HOP_DONG["TLEPHAT_BBAN"] = "";
                        drHDG_HOP_DONG["MA_CNANG"] = "-1";
                        drHDG_HOP_DONG["NGAY_TAO"] = DateTime.Now;
                        drHDG_HOP_DONG["NGUOI_TAO"] = nguoinhap;
                        drHDG_HOP_DONG["NGAY_SUA"] = DateTime.Now;
                        drHDG_HOP_DONG["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["HDG_HOP_DONG"].Rows.Add(drHDG_HOP_DONG);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_HDG_HOP_DONG(DsTableInsert, ref id_hopdong, ref maloi))
                        //if (!cls.insert_HDG_HOP_DONG(DsTableInsert, ref id_hopdong, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóa
                        DataRow drHDG_HOP_DONG_del = DsTableDelete.Tables["HDG_HOP_DONG"].NewRow();
                        drHDG_HOP_DONG_del["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_HOP_DONG_del["ID_HDONG"] = id_hopdong;
                        drHDG_HOP_DONG_del["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_HOP_DONG_del["TRANG_THAI"] = 1;
                        DsTableDelete.Tables["HDG_HOP_DONG"].Rows.Add(drHDG_HOP_DONG_del);
                        DsTableDelete.AcceptChanges();
                        //update lai matusinh của hợp đồng
                        bool hd = GlobalServices.Service_NPCUS.update_MATUSINH(strMa_dviqly, id_matusinhHD, id_HD_tusinh);
                        //bool hd = cls.update_MATUSINH(strMa_dviqly, id_matusinhHD, id_HD_tusinh);
                        //
                        #endregion

                        #region insert bảng HDG_KHACH_HANG
                        DsTableInsert.Tables["HDG_KHACH_HANG"].Rows.Clear();
                        DataRow drHDG_KHACH_HANG = DsTableInsert.Tables["HDG_KHACH_HANG"].NewRow();
                        drHDG_KHACH_HANG["MA_DVIQLY"] = strMa_dviqly;
                        //drHDG_HOP_DONG["ID_HDONG"] = rgINS[i]["MA_PTDIEN"].ToString();
                        drHDG_KHACH_HANG["MA_KHANG"] = ma_khang;
                        drHDG_KHACH_HANG["SO_NHA"] = "";
                        drHDG_KHACH_HANG["DUONG_PHO"] = rgINS[i]["F3"].ToString();
                        drHDG_KHACH_HANG["DCHI_HDON"] = rgINS[i]["F3"].ToString();
                        drHDG_KHACH_HANG["ID_HDONG"] = id_hopdong;
                        drHDG_KHACH_HANG["MASO_THUE"] = "";
                        drHDG_KHACH_HANG["TLE_THUE"] = 10;
                        drHDG_KHACH_HANG["LOAI_KHANG"] = 0;
                        drHDG_KHACH_HANG["MANHOM_KHANG"] = "NDAN";
                        drHDG_KHACH_HANG["MA_NN"] = rgINS[i]["F16"].ToString();
                        drHDG_KHACH_HANG["TEN_KHANG"] = rgINS[i]["F2"].ToString();
                        drHDG_KHACH_HANG["TENHDON_KHANG"] = rgINS[i]["F2"].ToString();
                        drHDG_KHACH_HANG["TENTAT_KHANG"] = "";
                        drHDG_KHACH_HANG["NGAY_SINH"] = string.Empty;
                        drHDG_KHACH_HANG["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_KHACH_HANG["TRANG_THAI"] = 1;
                        drHDG_KHACH_HANG["THANG"] = dtpThangNam.SelectedDate.Value.Month;
                        drHDG_KHACH_HANG["NAM"] = dtpThangNam.SelectedDate.Value.Year;
                        drHDG_KHACH_HANG["MA_DVIDCHINH"] = rgINS[i]["F9"].ToString();
                        drHDG_KHACH_HANG["TKHOAN_KHANG"] = "";
                        drHDG_KHACH_HANG["MA_NHANG"] = "";
                        drHDG_KHACH_HANG["MA_KHTT"] = ma_khang;
                        drHDG_KHACH_HANG["MA_LOAIDN"] = "";
                        drHDG_KHACH_HANG["DTHOAI"] = "";
                        drHDG_KHACH_HANG["FAX"] = "";
                        drHDG_KHACH_HANG["EMAIL"] = "";
                        drHDG_KHACH_HANG["WEBSITE"] = "";
                        drHDG_KHACH_HANG["GIOI_TINH"] = "";
                        drHDG_KHACH_HANG["MA_CNANG"] = "-1";
                        drHDG_KHACH_HANG["NGAY_TAO"] = DateTime.Now;
                        drHDG_KHACH_HANG["NGUOI_TAO"] = nguoinhap;
                        drHDG_KHACH_HANG["NGAY_SUA"] = DateTime.Now;
                        drHDG_KHACH_HANG["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["HDG_KHACH_HANG"].Rows.Add(drHDG_KHACH_HANG);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_HDG_KHACH_HANG(DsTableInsert, ref maloi))
                        //if (!cls.insert_HDG_KHACH_HANG(DsTableInsert, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóa
                        DataRow drHDG_KHACH_HANG_del = DsTableDelete.Tables["HDG_KHACH_HANG"].NewRow();
                        drHDG_KHACH_HANG_del["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_KHACH_HANG_del["MA_KHANG"] = ma_khang;
                        drHDG_KHACH_HANG_del["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_KHACH_HANG_del["TRANG_THAI"] = 1;
                        DsTableDelete.Tables["HDG_KHACH_HANG"].Rows.Add(drHDG_KHACH_HANG_del);
                        DsTableDelete.AcceptChanges();
                        bool kh = GlobalServices.Service_NPCUS.update_MATUSINH(strMa_dviqly, id_matusinhKH, id_KH_tusinh);
                        //bool kh = cls.update_MATUSINH(strMa_dviqly, id_matusinhKH, id_KH_tusinh);

                        #endregion

                        #region insert bảng HDG_DIEM_DO
                        DsTableInsert.Tables["HDG_DIEM_DO"].Rows.Clear();
                        DataRow drHDG_DIEM_DO = DsTableInsert.Tables["HDG_DIEM_DO"].NewRow();
                        drHDG_DIEM_DO["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_DIEM_DO["MA_DDO"] = ma_khang + "001";
                        drHDG_DIEM_DO["ID_HDONG"] = id_hopdong;
                        drHDG_DIEM_DO["MA_KHANG"] = ma_khang;
                        drHDG_DIEM_DO["MA_DDO_DDIEN"] = ma_khang + "001";
                        drHDG_DIEM_DO["MA_CAPDA"] = 1;
                        drHDG_DIEM_DO["DIA_CHI"] = rgINS[i]["F3"].ToString();
                        drHDG_DIEM_DO["ID_DIA_CHINH"] = id_DVDIACHINH;
                        drHDG_DIEM_DO["KIMUA_CSPK"] = 0;
                        drHDG_DIEM_DO["CSUAT"] = 0;
                        drHDG_DIEM_DO["MUC_DICH"] = "";
                        drHDG_DIEM_DO["SO_PHA"] = 1;
                        drHDG_DIEM_DO["SOHUU_LUOI"] = 1;
                        drHDG_DIEM_DO["LOAI_DDO"] = 1;
                        drHDG_DIEM_DO["SO_HO"] = rgINS[i]["F20"].ToString();
                        drHDG_DIEM_DO["LAN_CAPNHAT"] = 1;
                        drHDG_DIEM_DO["THAO_TACDL"] = "PT_NHANH";
                        drHDG_DIEM_DO["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_DIEM_DO["MO_TA"] = "";
                        drHDG_DIEM_DO["TRANG_THAI"] = 1;
                        drHDG_DIEM_DO["TTRANG_TREOTHAO"] = 1;
                        drHDG_DIEM_DO["MA_CNANG"] = "-1";
                        drHDG_DIEM_DO["NGAY_TAO"] = DateTime.Now;
                        drHDG_DIEM_DO["NGUOI_TAO"] = nguoinhap;
                        drHDG_DIEM_DO["NGAY_SUA"] = DateTime.Now;
                        drHDG_DIEM_DO["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["HDG_DIEM_DO"].Rows.Add(drHDG_DIEM_DO);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_HDG_DIEM_DO(DsTableInsert, ref maloi))
                        //if (!cls.insert_HDG_DIEM_DO(DsTableInsert, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóa
                        DataRow drHDG_DIEM_DO_del = DsTableDelete.Tables["HDG_DIEM_DO"].NewRow();
                        drHDG_DIEM_DO_del["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_DIEM_DO_del["MA_DDO"] = ma_khang + "001";
                        drHDG_DIEM_DO_del["LAN_CAPNHAT"] = 1;
                        drHDG_DIEM_DO_del["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        DsTableDelete.Tables["HDG_DIEM_DO"].Rows.Add(drHDG_DIEM_DO_del);
                        DsTableDelete.AcceptChanges();

                        #endregion

                        #region insert bảng HDG_DDO_SOGCS
                        DsTableInsert.Tables["HDG_DDO_SOGCS"].Rows.Clear();
                        DataRow drHDG_DDO_SOGCS = DsTableInsert.Tables["HDG_DDO_SOGCS"].NewRow();
                        drHDG_DDO_SOGCS["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_DDO_SOGCS["MA_SOGCS"] = rgINS[i]["F15"].ToString();
                        drHDG_DDO_SOGCS["MA_DDO"] = ma_khang + "001";
                        drHDG_DDO_SOGCS["MA_KHANG"] = ma_khang;
                        drHDG_DDO_SOGCS["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_DDO_SOGCS["MA_KVUC"] = "";
                        if (i < 10) drHDG_DDO_SOGCS["STT"] = "00" + i.ToString();
                        if (i >= 10 && i < 100) drHDG_DDO_SOGCS["STT"] = "0" + i.ToString();
                        if (i >= 100 && i < 100) drHDG_DDO_SOGCS["STT"] = i.ToString();
                        drHDG_DDO_SOGCS["KY"] = 1;
                        drHDG_DDO_SOGCS["THANG"] = dtpThangNam.SelectedDate.Value.Month;
                        drHDG_DDO_SOGCS["NAM"] = dtpThangNam.SelectedDate.Value.Year;
                        drHDG_DDO_SOGCS["MA_CNANG"] = "-1";
                        drHDG_DDO_SOGCS["NGAY_TAO"] = DateTime.Now;
                        drHDG_DDO_SOGCS["NGUOI_TAO"] = nguoinhap;
                        drHDG_DDO_SOGCS["NGAY_SUA"] = DateTime.Now;
                        drHDG_DDO_SOGCS["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["HDG_DDO_SOGCS"].Rows.Add(drHDG_DDO_SOGCS);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_HDG_DDO_SOGCS(DsTableInsert, ref id_qhe, ref maloi))
                        //if (!cls.insert_HDG_DDO_SOGCS(DsTableInsert,ref id_qhe, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóa
                        DataRow drHDG_DDO_SOGCS_del = DsTableDelete.Tables["HDG_DDO_SOGCS"].NewRow();
                        drHDG_DDO_SOGCS_del["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_DDO_SOGCS_del["ID_QHE"] = id_qhe;
                        drHDG_DDO_SOGCS_del["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        DsTableDelete.Tables["HDG_DDO_SOGCS"].Rows.Add(drHDG_DDO_SOGCS_del);
                        DsTableDelete.AcceptChanges();
                        #endregion

                        #region insert bảng HDG_VITRI_DDO
                        DsTableInsert.Tables["HDG_VITRI_DDO"].Rows.Clear();
                        DataRow drHDG_VITRI_DDO = DsTableInsert.Tables["HDG_VITRI_DDO"].NewRow();
                        drHDG_VITRI_DDO["MA_DVIQLY"] = strMa_dviqly;
                        //drHDG_VITRI_DDO["ID_VITRI_DDO"] = ma_khang + "001";
                        drHDG_VITRI_DDO["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_VITRI_DDO["MA_DDO"] = ma_khang + "001";
                        drHDG_VITRI_DDO["MA_TRAM"] = rgINS[i]["F13"].ToString();
                        drHDG_VITRI_DDO["MA_LO"] = rgINS[i]["F12"].ToString();
                        drHDG_VITRI_DDO["MA_TO"] = "";
                        drHDG_VITRI_DDO["PHA"] = "";
                        drHDG_VITRI_DDO["SO_COT"] = rgINS[i]["F10"].ToString();
                        drHDG_VITRI_DDO["SO_HOM"] = rgINS[i]["F11"].ToString();
                        drHDG_VITRI_DDO["MA_CNANG"] = "-1";
                        drHDG_VITRI_DDO["NGAY_TAO"] = DateTime.Now;
                        drHDG_VITRI_DDO["NGUOI_TAO"] = nguoinhap;
                        drHDG_VITRI_DDO["NGAY_SUA"] = DateTime.Now;
                        drHDG_VITRI_DDO["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["HDG_VITRI_DDO"].Rows.Add(drHDG_VITRI_DDO);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_HDG_VITRI_DDO(DsTableInsert, ref id_vitri_ddo, ref maloi))
                        //if (!cls.insert_HDG_VITRI_DDO(DsTableInsert,ref id_vitri_ddo, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóa
                        DataRow drHDG_VITRI_DDO_del = DsTableDelete.Tables["HDG_VITRI_DDO"].NewRow();
                        drHDG_VITRI_DDO_del["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_VITRI_DDO_del["ID_VITRI_DDO"] = id_vitri_ddo;
                        DsTableDelete.Tables["HDG_VITRI_DDO"].Rows.Add(drHDG_VITRI_DDO_del);
                        DsTableDelete.AcceptChanges();
                        #endregion

                        #region insert bảng HDG_PTHUC_TTOAN
                        DsTableInsert.Tables["HDG_PTHUC_TTOAN"].Rows.Clear();
                        DataRow drHDG_PTHUC_TTOAN = DsTableInsert.Tables["HDG_PTHUC_TTOAN"].NewRow();
                        drHDG_PTHUC_TTOAN["MA_DVIQLY"] = strMa_dviqly;
                        //drHDG_VITRI_DDO["ID_VITRI_DDO"] = ma_khang + "001";
                        drHDG_PTHUC_TTOAN["ID_HDONG"] = id_hopdong;
                        drHDG_PTHUC_TTOAN["MA_KHANG"] = ma_khang;
                        drHDG_PTHUC_TTOAN["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_PTHUC_TTOAN["TRANG_THAI"] = 1;
                        drHDG_PTHUC_TTOAN["HTHUC_TTOAN"] = rgINS[i]["F5"].ToString();
                        drHDG_PTHUC_TTOAN["PTHUC_TTOAN"] = "TM";
                        drHDG_PTHUC_TTOAN["TAI_KHOAN_DVI"] = "";
                        drHDG_PTHUC_TTOAN["MA_NHANG_DVI"] = "";
                        drHDG_PTHUC_TTOAN["MA_CNANG"] = "-1";
                        drHDG_PTHUC_TTOAN["NGAY_TAO"] = DateTime.Now;
                        drHDG_PTHUC_TTOAN["NGUOI_TAO"] = nguoinhap;
                        drHDG_PTHUC_TTOAN["NGAY_SUA"] = DateTime.Now;
                        drHDG_PTHUC_TTOAN["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["HDG_PTHUC_TTOAN"].Rows.Add(drHDG_PTHUC_TTOAN);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_HDG_PTHUC_TTOAN(DsTableInsert, ref id_pthuc_ttoan, ref maloi))
                        //if (!cls.insert_HDG_PTHUC_TTOAN(DsTableInsert, ref id_pthuc_ttoan, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóa
                        DataRow drHDG_PTHUC_TTOAN_del = DsTableDelete.Tables["HDG_PTHUC_TTOAN"].NewRow();
                        drHDG_PTHUC_TTOAN_del["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_PTHUC_TTOAN_del["ID_PTHUC_TTOAN"] = id_pthuc_ttoan;
                        DsTableDelete.Tables["HDG_PTHUC_TTOAN"].Rows.Add(drHDG_PTHUC_TTOAN_del);
                        DsTableDelete.AcceptChanges();
                        #endregion

                        #region insert bảng HDG_KHANG_LIENHE
                        DsTableInsert.Tables["HDG_KHANG_LIENHE"].Rows.Clear();
                        DataRow drHDG_KHANG_LIENHE = DsTableInsert.Tables["HDG_KHANG_LIENHE"].NewRow();
                        drHDG_KHANG_LIENHE["MA_DVIQLY"] = strMa_dviqly;
                        //drHDG_KHANG_LIENHE["ID_HDONG"] = rgINS[i]["MA_PTDIEN"].ToString();
                        drHDG_KHANG_LIENHE["MA_YCAU_KNAI"] = id_MaYeuCau;
                        drHDG_KHANG_LIENHE["MA_KHANG"] = ma_khang;
                        drHDG_KHANG_LIENHE["STTU_UTIEN"] = 0;
                        drHDG_KHANG_LIENHE["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_KHANG_LIENHE["TRANG_THAI"] = 1;
                        drHDG_KHANG_LIENHE["TEN_LIENLAC"] = rgINS[i]["F2"].ToString();
                        drHDG_KHANG_LIENHE["DCHI_NHA"] = rgINS[i]["F3"].ToString();
                        drHDG_KHANG_LIENHE["SO_CMT"] = rgINS[i]["F6"].ToString();
                        drHDG_KHANG_LIENHE["NGAY_CAP"] = converdate(rgINS[i]["F7"].ToString());
                        drHDG_KHANG_LIENHE["NOI_CAP"] = rgINS[i]["F8"].ToString();
                        drHDG_KHANG_LIENHE["SOGIAY_UQUYEN"] = "";
                        drHDG_KHANG_LIENHE["TEN_CQUAN"] = "";
                        drHDG_KHANG_LIENHE["CHUC_VU"] = "";
                        drHDG_KHANG_LIENHE["DCHI_CQUAN"] = "";
                        drHDG_KHANG_LIENHE["DTHOAI_DD"] = "";
                        drHDG_KHANG_LIENHE["DTHOAI_CQ"] = "";
                        drHDG_KHANG_LIENHE["DTHOAI_NR"] = "";
                        drHDG_KHANG_LIENHE["DTHOAI_KOGOI"] = "";
                        drHDG_KHANG_LIENHE["FAX"] = "";
                        drHDG_KHANG_LIENHE["EMAIL"] = "";
                        drHDG_KHANG_LIENHE["EMAIL2"] = "";
                        drHDG_KHANG_LIENHE["EMAIL_KOGUI"] = "";
                        drHDG_KHANG_LIENHE["WEBSITE"] = "";
                        drHDG_KHANG_LIENHE["GIOI_TINH"] = "";
                        drHDG_KHANG_LIENHE["NGAY_SINH"] = "";
                        drHDG_KHANG_LIENHE["NGHE_NGHIEP"] = "";
                        drHDG_KHANG_LIENHE["WEBSITE"] = "";
                        drHDG_KHANG_LIENHE["TRINH_DO"] = "";
                        drHDG_KHANG_LIENHE["MA_CNANG"] = "-1";
                        drHDG_KHANG_LIENHE["NGAY_TAO"] = DateTime.Now;
                        drHDG_KHANG_LIENHE["NGUOI_TAO"] = nguoinhap;
                        drHDG_KHANG_LIENHE["NGAY_SUA"] = DateTime.Now;
                        drHDG_KHANG_LIENHE["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["HDG_KHANG_LIENHE"].Rows.Add(drHDG_KHANG_LIENHE);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_HDG_KHANG_LIENHE(DsTableInsert, ref id_lienhe, ref maloi))
                        //if (!cls.insert_HDG_KHANG_LIENHE(DsTableInsert,ref id_lienhe, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóav
                        DataRow drHDG_KHANG_LIENHE_del = DsTableDelete.Tables["HDG_KHANG_LIENHE"].NewRow();
                        drHDG_KHANG_LIENHE_del["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_KHANG_LIENHE_del["ID_LIENHE"] = id_lienhe;
                        DsTableDelete.Tables["HDG_KHANG_LIENHE"].Rows.Add(drHDG_KHANG_LIENHE_del);
                        DsTableDelete.AcceptChanges();
                        #endregion

                        #region insert bảng DD_BBAN_BDONG
                        DsTableInsert.Tables["DD_BBAN_BDONG"].Rows.Clear();
                        DataRow drDD_BBAN_BDONG = DsTableInsert.Tables["DD_BBAN_BDONG"].NewRow();
                        drDD_BBAN_BDONG["MA_DVIQLY"] = strMa_dviqly;
                        //drHDG_HOP_DONG["ID_BBAN"] = rgINS[i]["MA_PTDIEN"].ToString();
                        drDD_BBAN_BDONG["MA_DDO"] = ma_khang + "001";
                        drDD_BBAN_BDONG["SO_BBAN"] = "";
                        drDD_BBAN_BDONG["NGAY_BDONG"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drDD_BBAN_BDONG["MA_LDO"] = "TM";
                        drDD_BBAN_BDONG["MA_NVIEN"] = "";
                        drDD_BBAN_BDONG["MA_NVKCHI"] = "";
                        drDD_BBAN_BDONG["NGAY_KCHI"] = string.Empty;
                        drDD_BBAN_BDONG["MA_CNANG"] = "-1";
                        drDD_BBAN_BDONG["NGAY_TAO"] = DateTime.Now;
                        drDD_BBAN_BDONG["NGUOI_TAO"] = nguoinhap;
                        drDD_BBAN_BDONG["NGAY_SUA"] = DateTime.Now;
                        drDD_BBAN_BDONG["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["DD_BBAN_BDONG"].Rows.Add(drDD_BBAN_BDONG);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_DD_BBAN_BDONG(DsTableInsert, ref id_bb_bdong, ref maloi))
                        //if (!cls.insert_DD_BBAN_BDONG(DsTableInsert,ref id_bb_bdong, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóav
                        DataRow drDD_BBAN_BDONG_del = DsTableDelete.Tables["DD_BBAN_BDONG"].NewRow();
                        drDD_BBAN_BDONG_del["MA_DVIQLY"] = strMa_dviqly;
                        drDD_BBAN_BDONG_del["ID_BBAN"] = id_bb_bdong;
                        DsTableDelete.Tables["DD_BBAN_BDONG"].Rows.Add(drDD_BBAN_BDONG_del);
                        DsTableDelete.AcceptChanges();
                        //
                        dsc = new DataSet();
                        dsc = GlobalServices.Service_NPCUS.check_cto(strMa_dviqly, rgINS[i]["F22"].ToString(), rgINS[i]["F21"].ToString(), rgINS[i]["F23"].ToString());
                        //dsc = cls.check_cto(strMa_dviqly, rgINS[i]["F22"].ToString(), rgINS[i]["F21"].ToString(), rgINS[i]["F23"].ToString());
                        bool hs = GlobalServices.Service_NPCUS.update_hoso_cto(strMa_dviqly, dsc.Tables[0].Rows[0]["MA_CTO"].ToString(), Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy")));
                        //bool hs = cls.update_hoso_cto(strMa_dviqly, dsc.Tables[0].Rows[0]["MA_CTO"].ToString(), Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy")));
                        DataRow drDD_HOSO_CTO_update = DsTableDelete.Tables["DD_HOSO_CTO"].NewRow();
                        drDD_HOSO_CTO_update["MA_DVIQLY"] = strMa_dviqly;
                        drDD_HOSO_CTO_update["MA_CTO"] = dsc.Tables[0].Rows[0]["MA_CTO"].ToString();
                        DsTableDelete.Tables["DD_HOSO_CTO"].Rows.Add(drDD_HOSO_CTO_update);
                        DsTableDelete.AcceptChanges();
                        #endregion

                        #region insert bảng DD_BCS_CTO
                        DsTableInsert.Tables["DD_BCS_CTO"].Rows.Clear();
                        DataRow drDD_BCS_CTO = DsTableInsert.Tables["DD_BCS_CTO"].NewRow();
                        drDD_BCS_CTO["MA_DVIQLY"] = strMa_dviqly;
                        //drDD_BCS_CTO["ID_BCS"] = rgINS[i]["MA_PTDIEN"].ToString();
                        drDD_BCS_CTO["MA_DDO"] = ma_khang + "001";
                        drDD_BCS_CTO["ID_BBAN"] = id_bb_bdong;
                        drDD_BCS_CTO["MA_CTO"] = dsc.Tables[0].Rows[0]["MA_CTO"].ToString();
                        drDD_BCS_CTO["SO_CTO"] = rgINS[i]["F22"].ToString();
                        drDD_BCS_CTO["BCS"] = "KT";
                        drDD_BCS_CTO["HS_NHAN"] = Convert.ToDecimal(rgINS[i]["F24"].ToString());
                        drDD_BCS_CTO["MA_CNANG"] = "-1";
                        drDD_BCS_CTO["NGAY_TAO"] = DateTime.Now;
                        drDD_BCS_CTO["NGUOI_TAO"] = nguoinhap;
                        drDD_BCS_CTO["NGAY_SUA"] = DateTime.Now;
                        drDD_BCS_CTO["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["DD_BCS_CTO"].Rows.Add(drDD_BCS_CTO);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_DD_BCS_CTO(DsTableInsert, ref id_bcs, ref maloi))
                        //if (!cls.insert_DD_BCS_CTO(DsTableInsert,ref id_bcs, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóav
                        DataRow drDD_BCS_CTO_del = DsTableDelete.Tables["DD_BCS_CTO"].NewRow();
                        drDD_BCS_CTO_del["MA_DVIQLY"] = strMa_dviqly;
                        drDD_BCS_CTO_del["ID_BCS"] = id_bcs;
                        DsTableDelete.Tables["DD_BCS_CTO"].Rows.Add(drDD_BCS_CTO_del);
                        DsTableDelete.AcceptChanges();
                        //
                        #endregion

                        #region insert bảng GCS_CHISO
                        DsTableInsert.Tables["GCS_CHISO"].Rows.Clear();
                        DataRow drGCS_CHISO = DsTableInsert.Tables["GCS_CHISO"].NewRow();
                        drGCS_CHISO["MA_DVIQLY"] = strMa_dviqly;
                        drGCS_CHISO["ID_BCS"] = id_bcs;
                        drGCS_CHISO["MA_DDO"] = ma_khang + "001";
                        drGCS_CHISO["BCS"] = "KT";
                        drGCS_CHISO["HS_NHAN"] = rgINS[i]["F24"].ToString();
                        drGCS_CHISO["MA_CTO"] = dsc.Tables[0].Rows[0]["MA_CTO"].ToString();
                        drGCS_CHISO["SO_CTO"] = rgINS[i]["F22"].ToString();
                        drGCS_CHISO["KY"] = 1;
                        drGCS_CHISO["THANG"] = dtpThangNam.SelectedDate.Value.Month;
                        drGCS_CHISO["NAM"] = dtpThangNam.SelectedDate.Value.Year;
                        if (string.IsNullOrEmpty(rgINS[i]["F25"].ToString()))
                        { drGCS_CHISO["CHISO_CU"] = 0; }
                        else
                        { drGCS_CHISO["CHISO_CU"] = Convert.ToDecimal(rgINS[i]["F25"].ToString()); }
                        if (string.IsNullOrEmpty(rgINS[i]["F25"].ToString()))
                        { drGCS_CHISO["CHISO_MOI"] = 0; }
                        else
                        { drGCS_CHISO["CHISO_MOI"] = Convert.ToDecimal(rgINS[i]["F25"].ToString()); }
                        drGCS_CHISO["SAN_LUONG"] = 0;
                        drGCS_CHISO["SLUONG_TTIEP"] = 0;
                        drGCS_CHISO["SLUONG_TRPHU"] = 0;
                        drGCS_CHISO["LOAI_CHISO"] = "DUP";
                        drGCS_CHISO["MA_TTCTO"] = "";
                        drGCS_CHISO["NGAY_DKY"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drGCS_CHISO["NGAY_CKY"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drGCS_CHISO["SLUONG_1"] = 0;
                        drGCS_CHISO["SLUONG_2"] = 0;
                        drGCS_CHISO["SLUONG_3"] = 0;
                        drGCS_CHISO["MA_KHANG"] = ma_khang;
                        drGCS_CHISO["MA_CNANG"] = "-1";
                        drGCS_CHISO["NGAY_TAO"] = DateTime.Now;
                        drGCS_CHISO["NGUOI_TAO"] = nguoinhap;
                        drGCS_CHISO["NGAY_SUA"] = DateTime.Now;
                        drGCS_CHISO["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["GCS_CHISO"].Rows.Add(drGCS_CHISO);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_GCS_CHISO(DsTableInsert, ref id_chiso, ref maloi))
                        //if (!cls.insert_GCS_CHISO(DsTableInsert,ref id_chiso, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóa
                        DataRow drGCS_CHISO_del = DsTableDelete.Tables["GCS_CHISO"].NewRow();
                        drGCS_CHISO_del["MA_DVIQLY"] = strMa_dviqly;
                        drGCS_CHISO_del["ID_CHISO"] = id_chiso;
                        DsTableDelete.Tables["GCS_CHISO"].Rows.Add(drGCS_CHISO_del);
                        DsTableDelete.AcceptChanges();
                        //
                        #endregion

                        #region insert bảng DD_BDONG_CTO
                        DsTableInsert.Tables["DD_BDONG_CTO"].Rows.Clear();
                        DataRow drDD_BDONG_CTO = DsTableInsert.Tables["DD_BDONG_CTO"].NewRow();
                        drDD_BDONG_CTO["MA_DVIQLY"] = strMa_dviqly;
                        drDD_BDONG_CTO["ID_BBAN"] = id_bb_bdong;
                        drDD_BDONG_CTO["MA_DDO"] = ma_khang + "001";
                        drDD_BDONG_CTO["MA_CTO"] = dsc.Tables[0].Rows[0]["MA_CTO"].ToString();
                        drDD_BDONG_CTO["SO_CTO"] = rgINS[i]["F22"].ToString();
                        drDD_BDONG_CTO["LAN"] = 1;
                        drDD_BDONG_CTO["MA_BDONG"] = "B";
                        drDD_BDONG_CTO["NGAY_BDONG"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drDD_BDONG_CTO["MA_CLOAI"] = rgINS[i]["F21"].ToString();
                        drDD_BDONG_CTO["LOAI_CTO"] = "HC";
                        drDD_BDONG_CTO["VTRI_TREO"] = 0;
                        drDD_BDONG_CTO["MA_SOCBOOC"] = "";
                        drDD_BDONG_CTO["SO_VIENCBOOC"] = "";
                        drDD_BDONG_CTO["MA_SOCHOM"] = "";
                        drDD_BDONG_CTO["SO_VIENCHOM"] = "";
                        drDD_BDONG_CTO["SO_COT"] = rgINS[i]["F10"].ToString();
                        drDD_BDONG_CTO["SO_HOM"] = rgINS[i]["F11"].ToString();
                        drDD_BDONG_CTO["MA_CNANG"] = "-1";
                        drDD_BDONG_CTO["NGAY_TAO"] = DateTime.Now;
                        drDD_BDONG_CTO["NGUOI_TAO"] = nguoinhap;
                        drDD_BDONG_CTO["NGAY_SUA"] = DateTime.Now;
                        drDD_BDONG_CTO["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["DD_BDONG_CTO"].Rows.Add(drDD_BDONG_CTO);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_DD_BDONG_CTO(DsTableInsert, ref maloi))
                        //if (!cls.insert_DD_BDONG_CTO(DsTableInsert, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóav
                        DataRow drDD_BDONG_CTO_del = DsTableDelete.Tables["DD_BDONG_CTO"].NewRow();
                        drDD_BDONG_CTO_del["MA_DVIQLY"] = strMa_dviqly;
                        drDD_BDONG_CTO_del["ID_BBAN"] = id_bb_bdong;
                        drDD_BDONG_CTO_del["MA_DDO"] = ma_khang + "001";
                        drDD_BDONG_CTO_del["MA_CTO"] = dsc.Tables[0].Rows[0]["MA_CTO"].ToString();
                        drDD_BDONG_CTO_del["LAN"] = 1;
                        DsTableDelete.Tables["DD_BDONG_CTO"].Rows.Add(drDD_BDONG_CTO_del);
                        DsTableDelete.AcceptChanges();
                        //
                        #endregion

                        #region insert bảng DD_BDONG_HTAI
                        DsTableInsert.Tables["DD_BDONG_HTAI"].Rows.Clear();
                        DataRow drDD_BDONG_HTAI = DsTableInsert.Tables["DD_BDONG_HTAI"].NewRow();
                        drDD_BDONG_HTAI["MA_DVIQLY"] = strMa_dviqly;
                        drDD_BDONG_HTAI["MA_DDO"] = ma_khang + "001";
                        drDD_BDONG_HTAI["MA_TBI"] = dsc.Tables[0].Rows[0]["MA_CTO"].ToString();
                        drDD_BDONG_HTAI["SO_TBI"] = rgINS[i]["F22"].ToString();
                        drDD_BDONG_HTAI["LOAI_TBI"] = "CTOHC";
                        drDD_BDONG_HTAI["NGAY_BDONG"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drDD_BDONG_HTAI["MA_CLOAI"] = rgINS[i]["F21"].ToString();
                        drDD_BDONG_HTAI["MA_CNANG"] = "-1";
                        drDD_BDONG_HTAI["NGAY_TAO"] = DateTime.Now;
                        drDD_BDONG_HTAI["NGUOI_TAO"] = nguoinhap;
                        drDD_BDONG_HTAI["NGAY_SUA"] = DateTime.Now;
                        drDD_BDONG_HTAI["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["DD_BDONG_HTAI"].Rows.Add(drDD_BDONG_HTAI);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_DD_BDONG_HTAI(DsTableInsert, ref maloi))
                        //if (!cls.insert_DD_BDONG_HTAI(DsTableInsert, ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóav
                        DataRow drDD_BDONG_HTAI_del = DsTableDelete.Tables["DD_BDONG_HTAI"].NewRow();
                        drDD_BDONG_HTAI_del["MA_DVIQLY"] = strMa_dviqly;
                        drDD_BDONG_HTAI_del["MA_DDO"] = ma_khang + "001";
                        drDD_BDONG_HTAI_del["MA_TBI"] = dsc.Tables[0].Rows[0]["MA_CTO"].ToString();
                        drDD_BDONG_HTAI_del["LOAI_TBI"] = "CTOHC";
                        DsTableDelete.Tables["DD_BDONG_HTAI"].Rows.Add(drDD_BDONG_HTAI_del);
                        DsTableDelete.AcceptChanges();
                        #endregion

                        #region insert bảng HDG_BBAN_APGIA
                        DsTableInsert.Tables["HDG_BBAN_APGIA"].Rows.Clear();
                        DataRow drHDG_BBAN_APGIA = DsTableInsert.Tables["HDG_BBAN_APGIA"].NewRow();
                        drHDG_BBAN_APGIA["MA_DVIQLY"] = strMa_dviqly;
                        //drHDG_BBAN_APGIA["ID_BBANAGIA"] = id_bcs;
                        drHDG_BBAN_APGIA["MA_BBANAGIA"] = "";
                        drHDG_BBAN_APGIA["MA_DDO"] = ma_khang + "001";
                        drHDG_BBAN_APGIA["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                        drHDG_BBAN_APGIA["SO_THUTU"] = 0;
                        drHDG_BBAN_APGIA["DINH_MUC"] = "100";
                        drHDG_BBAN_APGIA["LOAI_DMUC"] = "%";
                        drHDG_BBAN_APGIA["LOAI_BCS"] = "KT";
                        drHDG_BBAN_APGIA["TGIAN_BDIEN"] = "KT";
                        drHDG_BBAN_APGIA["MA_NHOMNN"] = rgINS[i]["F17"].ToString();
                        if (rgINS[i]["F18"].ToString() == "N") drHDG_BBAN_APGIA["MA_NGIA"] = "N";
                        if (rgINS[i]["F18"].ToString() != "N") drHDG_BBAN_APGIA["MA_NGIA"] = "A";
                        drHDG_BBAN_APGIA["MA_NN"] = rgINS[i]["F16"].ToString();
                        drHDG_BBAN_APGIA["TRANG_THAI"] = 1;
                        drHDG_BBAN_APGIA["SO_HO"] = rgINS[i]["F20"].ToString();
                        drHDG_BBAN_APGIA["MA_CAPDAP"] = "1";
                        drHDG_BBAN_APGIA["DIEN_GIAI"] = "SH - Sinh hoạt bậc thang Dưới 380V";
                        drHDG_BBAN_APGIA["MA_CNANG"] = "-1";
                        drHDG_BBAN_APGIA["NGAY_TAO"] = DateTime.Now;
                        drHDG_BBAN_APGIA["NGUOI_TAO"] = nguoinhap;
                        drHDG_BBAN_APGIA["NGAY_SUA"] = DateTime.Now;
                        drHDG_BBAN_APGIA["NGUOI_SUA"] = nguoinhap;
                        DsTableInsert.Tables["HDG_BBAN_APGIA"].Rows.Add(drHDG_BBAN_APGIA);
                        DsTableInsert.AcceptChanges();
                        if (!GlobalServices.Service_NPCUS.insert_HDG_BBAN_APGIA(DsTableInsert, ref id_bbanagia, ref maloi))
                        //if (!cls.insert_HDG_BBAN_APGIA(DsTableInsert,ref id_bbanagia ,ref maloi))
                        { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                        //insert pk để xóav
                        DataRow drHDG_BBAN_APGIA_del = DsTableDelete.Tables["HDG_BBAN_APGIA"].NewRow();
                        drHDG_BBAN_APGIA_del["MA_DVIQLY"] = strMa_dviqly;
                        drHDG_BBAN_APGIA_del["MA_DDO"] = ma_khang + "001";
                        drHDG_BBAN_APGIA_del["ID_BBANAGIA"] = id_bbanagia;
                        drHDG_BBAN_APGIA_del["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy")); ;
                        DsTableDelete.Tables["HDG_BBAN_APGIA"].Rows.Add(drHDG_BBAN_APGIA_del);
                        DsTableDelete.AcceptChanges();
                        #endregion

                        #region insert bảng HDG_HO_NGHEO
                        if (rgINS[i]["F18"].ToString().Trim() == "N")// nếu có hộ nghèo mới insert vào bảng 
                        {
                            DsTableInsert.Tables["HDG_HO_NGHEO"].Rows.Clear();
                            DataRow drHDG_HO_NGHEO = DsTableInsert.Tables["HDG_HO_NGHEO"].NewRow();
                            drHDG_HO_NGHEO["MA_DVIQLY"] = strMa_dviqly;
                            //drHDG_HO_NGHEO["ID_HONGHEO"] = rgINS[i]["MA_PTDIEN"].ToString();
                            drHDG_HO_NGHEO["TEN_DKY"] = rgINS[i]["F2"].ToString();
                            drHDG_HO_NGHEO["DCHI_DKY"] = rgINS[i]["F3"].ToString();
                            drHDG_HO_NGHEO["MA_KHANG"] = ma_khang;
                            drHDG_HO_NGHEO["MA_DDO"] = ma_khang + "001";
                            drHDG_HO_NGHEO["TEN_KHANG"] = rgINS[i]["F2"].ToString();
                            drHDG_HO_NGHEO["DCHI_KHANG"] = rgINS[i]["F3"].ToString();
                            drHDG_HO_NGHEO["LOAI_KHANG"] = 0;
                            drHDG_HO_NGHEO["NGAY_DKY"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                            drHDG_HO_NGHEO["SDT_DKY"] = "";
                            drHDG_HO_NGHEO["THANG_APDUNG"] = dtpThangNam.SelectedDate.Value.Month;
                            drHDG_HO_NGHEO["NAM_APDUNG"] = dtpThangNam.SelectedDate.Value.Year;
                            drHDG_HO_NGHEO["THANG_HUY"] = string.Empty;
                            drHDG_HO_NGHEO["NAM_HUY"] = string.Empty;
                            drHDG_HO_NGHEO["TRANG_THAI"] = 1;
                            drHDG_HO_NGHEO["GHI_CHU"] = "";
                            drHDG_HO_NGHEO["NGAY_TAO"] = DateTime.Now;
                            drHDG_HO_NGHEO["NGUOI_TAO"] = nguoinhap;
                            drHDG_HO_NGHEO["NGAY_SUA"] = DateTime.Now;
                            drHDG_HO_NGHEO["NGUOI_SUA"] = nguoinhap;
                            DsTableInsert.Tables["HDG_HO_NGHEO"].Rows.Add(drHDG_HO_NGHEO);
                            DsTableInsert.AcceptChanges();
                            if (!GlobalServices.Service_NPCUS.insert_HDG_HO_NGHEO(DsTableInsert, ref id_hongheo, ref maloi))
                            //if (!cls.insert_HDG_HO_NGHEO(DsTableInsert, ref id_hongheo, ref maloi))
                            { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                            //insert pk để xóav
                            DataRow drHDG_HO_NGHEO_del = DsTableDelete.Tables["HDG_HO_NGHEO"].NewRow();
                            drHDG_HO_NGHEO_del["MA_DVIQLY"] = strMa_dviqly;
                            drHDG_HO_NGHEO_del["ID_HONGHEO"] = id_hongheo;
                            DsTableDelete.Tables["HDG_HO_NGHEO"].Rows.Add(drHDG_HO_NGHEO_del);
                            DsTableDelete.AcceptChanges();
                        }
                        #endregion

                            #region insert bảng DD_BDONG_TTHAO
                            DsTableInsert.Tables["DD_BDONG_TTHAO"].Rows.Clear();
                            DataRow drDD_BDONG_TTHAO = DsTableInsert.Tables["DD_BDONG_TTHAO"].NewRow();
                            drDD_BDONG_TTHAO["MA_DVIQLY"] = strMa_dviqly;
                            //drDD_BDONG_TTHAO["ID_LSU_TTHAO"] = rgINS[i]["F2"].ToString();
                            drDD_BDONG_TTHAO["ID_BBAN"] = id_bb_bdong;
                            drDD_BDONG_TTHAO["MA_KHANG"] = ma_khang;
                            drDD_BDONG_TTHAO["MA_DDO"] = ma_khang + "001";
                            drDD_BDONG_TTHAO["TEN_KHANG"] = rgINS[i]["F2"].ToString();
                            drDD_BDONG_TTHAO["DCHI_KHANG"] = rgINS[i]["F3"].ToString();
                            drDD_BDONG_TTHAO["DCHI_DDO"] = rgINS[i]["F3"].ToString();
                            drDD_BDONG_TTHAO["MA_SOGCS"] = rgINS[i]["F15"].ToString();
                            drDD_BDONG_TTHAO["MA_TRAM"] = rgINS[i]["F13"].ToString();
                            drDD_BDONG_TTHAO["KVUC_STT"] = "";
                            drDD_BDONG_TTHAO["LOAI_DDO"] = 1;
                            drDD_BDONG_TTHAO["DDO_KHANG"] = "KH";
                            drDD_BDONG_TTHAO["MA_BDONG"] = "B";
                            drDD_BDONG_TTHAO["SO_PHA"] = 1;
                            drDD_BDONG_TTHAO["LOAI_KHANG"] = 0;
                            drDD_BDONG_TTHAO["NGAY_BDONG"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                            drDD_BDONG_TTHAO["NGAY_KCHI"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                            drDD_BDONG_TTHAO["MA_NVIEN"] = "";
                            drDD_BDONG_TTHAO["MA_NVKCHI"] = "";
                            drDD_BDONG_TTHAO["MA_LDO"] = "TM";
                            drDD_BDONG_TTHAO["MA_TBI"] = dsc.Tables[0].Rows[0]["MA_CTO"].ToString();
                            drDD_BDONG_TTHAO["LOAI_TBI"] = "CTO";
                            drDD_BDONG_TTHAO["SO_TBI"] = rgINS[i]["F22"].ToString();
                            drDD_BDONG_TTHAO["MA_CLOAI"] = rgINS[i]["F21"].ToString();
                            drDD_BDONG_TTHAO["VTRI_TREO"] = 0;
                            drDD_BDONG_TTHAO["BCS"] = "KT";
                            drDD_BDONG_TTHAO["LOAI_CHISO"] = "DUP";
                            drDD_BDONG_TTHAO["SO_COT"] = rgINS[i]["F10"].ToString();
                            drDD_BDONG_TTHAO["SO_HOM"] = rgINS[i]["F11"].ToString();
                            if (string.IsNullOrEmpty(rgINS[i]["F25"].ToString()))
                            { drDD_BDONG_TTHAO["CHISO_CU"] = 0; }
                            else
                            { drDD_BDONG_TTHAO["CHISO_CU"] = Convert.ToDecimal(rgINS[i]["F25"].ToString()); }
                            if (string.IsNullOrEmpty(rgINS[i]["F25"].ToString()))
                            { drDD_BDONG_TTHAO["CHISO_MOI"] = 0; }
                            else
                            { drDD_BDONG_TTHAO["CHISO_MOI"] = Convert.ToDecimal(rgINS[i]["F25"].ToString()); }
                            drDD_BDONG_TTHAO["SAN_LUONG"] = 0;
                            drDD_BDONG_TTHAO["SLUONG_TTIEP"] = 0;
                            drDD_BDONG_TTHAO["HS_NHAN"] = rgINS[i]["F24"].ToString();
                            drDD_BDONG_TTHAO["MA_CNANG"] = "-1";
                            drDD_BDONG_TTHAO["NGAY_TAO"] = DateTime.Now;
                            drDD_BDONG_TTHAO["NGUOI_TAO"] = nguoinhap;
                            drDD_BDONG_TTHAO["NGAY_SUA"] = DateTime.Now;
                            drDD_BDONG_TTHAO["NGUOI_SUA"] = nguoinhap;
                            DsTableInsert.Tables["DD_BDONG_TTHAO"].Rows.Add(drDD_BDONG_TTHAO);
                            DsTableInsert.AcceptChanges();
                            if (!GlobalServices.Service_NPCUS.insert_DD_BDONG_TTHAO(DsTableInsert, ref ID_LSU_TTHAO, ref maloi))
                            //if (!cls.insert_HDG_HO_NGHEO(DsTableInsert, ref id_hongheo, ref maloi))
                            { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                            //insert pk để xóav
                            DataRow drDD_BDONG_TTHAO_del = DsTableDelete.Tables["DD_BDONG_TTHAO"].NewRow();
                            drDD_BDONG_TTHAO_del["MA_DVIQLY"] = strMa_dviqly;
                            drDD_BDONG_TTHAO_del["ID_LSU_TTHAO"] = ID_LSU_TTHAO;
                            DsTableDelete.Tables["DD_BDONG_TTHAO"].Rows.Add(drDD_BDONG_TTHAO_del);
                            DsTableDelete.AcceptChanges();
                            #endregion

                            #region insert bảng HDG_PHU_LUC_HD
                            DsTableInsert.Tables["HDG_PHU_LUC_HD"].Rows.Clear();
                            DataRow drHDG_PHU_LUC_HD = DsTableInsert.Tables["HDG_PHU_LUC_HD"].NewRow();
                            drHDG_PHU_LUC_HD["MA_DVIQLY"] = strMa_dviqly;
                            //drHDG_PHU_LUC_HD["ID_PHU_LUC_HD"] = rgINS[i]["F2"].ToString();
                            drHDG_PHU_LUC_HD["ID_HDONG"] = id_hopdong;
                            drHDG_PHU_LUC_HD["NGAY_HLUC"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                            drHDG_PHU_LUC_HD["DDIEM_DDIEN"] = rgINS[i]["F3"].ToString();
                            drHDG_PHU_LUC_HD["MUC_DICHSD"] = "Sinh hoat";
                            drHDG_PHU_LUC_HD["DIEM_DAUNOI"] = rgINS[i]["F3"].ToString();
                            drHDG_PHU_LUC_HD["TGIAN_CAPDIEN"] = Convert.ToDateTime(dtpThangNam.SelectedDate.Value.ToString("MM/dd/yyyy"));
                            drHDG_PHU_LUC_HD["DIEN_AP"] = 220;
                            drHDG_PHU_LUC_HD["DONVI_DIENAP"] = "V";
                            drHDG_PHU_LUC_HD["SSODAP_BT"] = 0;
                            drHDG_PHU_LUC_HD["SSODAP_SCTU"] = 0;
                            drHDG_PHU_LUC_HD["SSODAP_SCDEN"] = 0;
                            drHDG_PHU_LUC_HD["VITRI_DODEM"] = "CMIS";
                            drHDG_PHU_LUC_HD["RGIOI_SOHUU"] = "CMIS";
                            drHDG_PHU_LUC_HD["RGIOI_VHANH"] = "CMIS";
                            drDD_BDONG_TTHAO["MA_CNANG"] = "-1";
                            drDD_BDONG_TTHAO["NGAY_TAO"] = DateTime.Now;
                            drDD_BDONG_TTHAO["NGUOI_TAO"] = nguoinhap;
                            drDD_BDONG_TTHAO["NGAY_SUA"] = DateTime.Now;
                            drDD_BDONG_TTHAO["NGUOI_SUA"] = nguoinhap;
                            DsTableInsert.Tables["HDG_PHU_LUC_HD"].Rows.Add(drHDG_PHU_LUC_HD);
                            DsTableInsert.AcceptChanges();
                            if (!GlobalServices.Service_NPCUS.insert_HDG_PHU_LUC_HD(DsTableInsert, ref ID_PHU_LUC_HD, ref maloi))
                            //if (!cls.insert_HDG_HO_NGHEO(DsTableInsert, ref id_hongheo, ref maloi))
                            { SetStatus("ERROR", cmdImportExcel, maloi); xoa_dulieu_loi(); return; }
                            //insert pk để xóav
                            DataRow drHDG_PHU_LUC_HD_del = DsTableDelete.Tables["HDG_PHU_LUC_HD"].NewRow();
                            drHDG_PHU_LUC_HD_del["MA_DVIQLY"] = strMa_dviqly;
                            drHDG_PHU_LUC_HD_del["ID_PHU_LUC_HD"] = ID_LSU_TTHAO;
                            DsTableDelete.Tables["HDG_PHU_LUC_HD"].Rows.Add(drHDG_PHU_LUC_HD_del);
                            DsTableDelete.AcceptChanges();
                            #endregion
                            // chay ProgressBar
                            //int ts = Convert.ToInt32((i + 1) / dtExecl.Rows.Count);
                            Decimal pr = Convert.ToDecimal(((i + 1) / dtExecl.Rows.Count) * 100);
                            Decimal pr1 = Math.Round((decimal)pr);
                            proBExcel.Value = (double)pr1;
                            txtphantram.Content = pr1.ToString() + "%";
                            txtts.Content = (i + 1).ToString() + "/" + dtExecl.Rows.Count.ToString();
                            //
                    }
                    SetStatus("COMPLATE", cmdImportExcel, "Cập nhật thành công!");
                    cmdImportExcel.IsEnabled = false;
                }
                else
                {
                    SetStatus("ERROR", cmdImportExcel, "Không có dữ liệu cập nhật!");
                    return;
                }
            }
            catch(Exception ex)
            { SetStatus("ERROR", cmdImportExcel, ex.ToString() + maloi); xoa_dulieu_loi(); return; }
        }
        protected DateTime converdate(string date)
        {
            DateTime d;
            try
            {
                string[] path = date.Split('/');
                d = Convert.ToDateTime(path[1].Trim().ToString() +"/" + path[0].Trim().ToString() + "/" + path[2].Trim().ToString());
                return d;
            }
            catch
            { return System.DateTime.Now; }
        }
        protected void xoa_dulieu_loi()
        {
            
            bool dl_HDG_HO_NGHEO = GlobalServices.Service_NPCUS.delete_HDG_HO_NGHEO(DsTableDelete, ref maloi);
            bool dl_HDG_BBAN_APGIA = GlobalServices.Service_NPCUS.delete_HDG_BBAN_APGIA(DsTableDelete, ref maloi);
            bool dl_GCS_CHISO = GlobalServices.Service_NPCUS.delete_GCS_CHISO(DsTableDelete, ref maloi);
            bool dl_DD_BDONG_HTAI = GlobalServices.Service_NPCUS.delete_DD_BDONG_HTAI(DsTableDelete, ref maloi);
            bool dl_DD_BDONG_CTO = GlobalServices.Service_NPCUS.delete_DD_BDONG_CTO(DsTableDelete, ref maloi);
            bool dl_DD_BCS_CTO = GlobalServices.Service_NPCUS.delete_DD_BCS_CTO(DsTableDelete, ref maloi);
            bool dl_DD_BBAN_BDONG = GlobalServices.Service_NPCUS.delete_DD_BBAN_BDONG(DsTableDelete, ref maloi);
            bool dl_DD_HOSO_CTO = GlobalServices.Service_NPCUS.update_DD_HOSO_CTO(DsTableDelete, ref maloi);
            bool dl_HDG_VITRI_DDO = GlobalServices.Service_NPCUS.delete_HDG_VITRI_DDO(DsTableDelete, ref maloi);
            bool dl_HDG_DDO_SOGCS = GlobalServices.Service_NPCUS.delete_HDG_DDO_SOGCS(DsTableDelete, ref maloi);
            bool dl_HDG_DIEM_DO = GlobalServices.Service_NPCUS.delete_HDG_DIEM_DO(DsTableDelete, ref maloi);
            bool dl_HDG_PTHUC_TTOAN = GlobalServices.Service_NPCUS.delete_HDG_PTHUC_TTOAN(DsTableDelete, ref maloi);
            bool dl_HDG_KHANG_LIENHE = GlobalServices.Service_NPCUS.delete_HDG_KHANG_LIENHE(DsTableDelete, ref maloi);
            bool dl_HDG_KHACH_HANG = GlobalServices.Service_NPCUS.delete_HDG_KHACH_HANG(DsTableDelete, ref maloi);
            bool dl_HDG_HOP_DONG = GlobalServices.Service_NPCUS.delete_HDG_HOP_DONG(DsTableDelete, ref maloi);
            bool dl_DV_YCAU_KNAI = GlobalServices.Service_NPCUS.delete_DV_YCAU_KNAI(DsTableDelete, ref maloi);
            bool dl_DD_BDONG_TTHAO = GlobalServices.Service_NPCUS.delete_DD_BDONG_TTHAO(DsTableDelete, ref maloi);
            bool dl_HDG_PHU_LUC_HD = GlobalServices.Service_NPCUS.delete_HDG_PHU_LUC_HD(DsTableDelete, ref maloi);

            /*
         bool dl_HDG_HO_NGHEO = cls.delete_HDG_HO_NGHEO(DsTableDelete.Tables["HDG_HO_NGHEO"],ref maloi);
         bool dl_HDG_BBAN_APGIA = cls.delete_HDG_BBAN_APGIA(DsTableDelete.Tables["HDG_BBAN_APGIA"], ref maloi);
         bool dl_GCS_CHISO = cls.delete_GCS_CHISO(DsTableDelete.Tables["GCS_CHISO"], ref maloi);
         bool dl_DD_BDONG_HTAI = cls.delete_DD_BDONG_HTAI(DsTableDelete.Tables["DD_BDONG_HTAI"], ref maloi);
         bool dl_DD_BDONG_CTO = cls.delete_DD_BDONG_CTO(DsTableDelete.Tables["DD_BDONG_CTO"], ref maloi);
         bool dl_DD_BCS_CTO = cls.delete_DD_BCS_CTO(DsTableDelete.Tables["DD_BCS_CTO"], ref maloi);
         bool dl_DD_BBAN_BDONG = cls.delete_DD_BBAN_BDONG(DsTableDelete.Tables["DD_BBAN_BDONG"], ref maloi);
         bool dl_DD_HOSO_CTO = cls.update_DD_HOSO_CTO(DsTableDelete.Tables["DD_HOSO_CTO"], ref maloi);
         bool dl_HDG_VITRI_DDO = cls.delete_HDG_VITRI_DDO(DsTableDelete.Tables["HDG_VITRI_DDO"], ref maloi);
         bool dl_HDG_DDO_SOGCS = cls.delete_HDG_DDO_SOGCS(DsTableDelete.Tables["HDG_DDO_SOGCS"], ref maloi);
         bool dl_HDG_DIEM_DO = cls.delete_HDG_DIEM_DO(DsTableDelete.Tables["HDG_DIEM_DO"], ref maloi);
         bool dl_HDG_PTHUC_TTOAN = cls.delete_HDG_PTHUC_TTOAN(DsTableDelete.Tables["HDG_PTHUC_TTOAN"], ref maloi);
         bool dl_HDG_KHANG_LIENHE = cls.delete_HDG_KHANG_LIENHE(DsTableDelete.Tables["HDG_KHANG_LIENHE"], ref maloi);
         bool dl_HDG_KHACH_HANG = cls.delete_HDG_KHACH_HANG(DsTableDelete.Tables["HDG_KHACH_HANG"], ref maloi);
         bool dl_HDG_HOP_DONG = cls.delete_HDG_HOP_DONG(DsTableDelete.Tables["HDG_HOP_DONG"], ref maloi);
         bool dl_DV_YCAU_KNAI = cls.delete_DV_YCAU_KNAI(DsTableDelete.Tables["DV_YCAU_KNAI"], ref maloi);
             *    */
        }
        protected void SetStatus(string Begin, System.Windows.Controls.Control ControlA, string strNote)
        {
            switch (Begin)
            {
                case "ERROR":
                    this.Cursor = null;
                    ControlA.Cursor = null;
                    Component.Validate.ValidationManager.showMessOnView(this, 10000, strNote, Component.Validate.ValidationManager.MESSEGER_TYPE.ERROR_MESSAGE);
                    break;
                case "OK":
                    lblstatus.Foreground = Brushes.Blue;
                    lblstatus.Content = strNote;
                    this.Cursor = System.Windows.Input.Cursors.Wait;
                    ControlA.Cursor = System.Windows.Input.Cursors.Wait;
                    System.Windows.Application.Current.Dispatcher.Invoke(DispatcherPriority.Background, new ThreadStart(delegate { }));
                    break;
                case "COMPLATE":
                    this.Cursor = null;
                    ControlA.Cursor = null;
                    Component.Validate.ValidationManager.showMessOnView(this, 10000, strNote, Component.Validate.ValidationManager.MESSEGER_TYPE.SUCCESS_MESSAGE);
                    break;
            }
        }
    }
}